﻿using System.Data;
using System.Text;
using System.ComponentModel;
using System.Text.RegularExpressions;

using Newtonsoft.Json;
using Microsoft.Data.SqlClient;
using ModelContextProtocol.Server;
using Microsoft.Extensions.Logging;
using ModelContextProtocol.Protocol;
using edan.zhjk.Commons.DBOption;
using Furion;

namespace edan.zhjk.Commons.MCPService.SqlServerDB
{
    /// <summary>
    /// SqlServer数据库
    /// </summary>
    [McpServerToolType, Description("SqlServer数据库")]
    public sealed class SqlServerDBTool
    {
        private readonly ILogger<SqlServerDBTool> _logger;
        private static MutiDBConns? MutiDBConns;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="logger"></param>
        public SqlServerDBTool(ILogger<SqlServerDBTool> logger)
        {
            _logger = logger;
            var dbOptions = App.GetOptionsMonitor<DBOptions>();
            MutiDBConns = dbOptions?.MutiDBConns?.FirstOrDefault(it => it.DbType == DataBaseType.SqlServer);
        }

        /// <summary>
        /// 读取PGSql连接
        /// </summary>
        /// <param name="url">数据库链接地址</param>
        /// <returns></returns>
        /// <exception cref="InvalidOperationException"></exception>
        private SqlConnection GetSqlServerConnection(string? url)
        {
            var finalUrl = string.IsNullOrWhiteSpace(url) ? MutiDBConns?.Connection : url;

            if (string.IsNullOrWhiteSpace(finalUrl))
                throw new InvalidOperationException("数据库链接地址字段是必需的，但未在环境中提供或设置。");

            return new SqlConnection(finalUrl);
        }

        /// <summary>
        /// 这是让人工智能理解数据库结构的工具
        /// </summary>
        /// <returns></returns>
        [McpServerTool(Name = "sqlserver_get_table_schemas"), Description("[SqlServer]获取所有表的列表，包括它们各自的结构、列以及数据类型")]
        public async Task<CallToolResult> GetTableSchemas([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetSqlServerConnection(url);
                await conn.OpenAsync(cancellationToken);

                using var command = new SqlCommand(@"
                    SELECT 
                        t.table_schema,
                        t.table_name, 
                        c.column_name, 
                        c.data_type 
                    FROM 
                        information_schema.tables t
                    JOIN 
                        information_schema.columns c 
                        ON t.table_name = c.table_name
                        AND t.table_schema = c.table_schema
                    WHERE 
                        t.table_type = 'BASE TABLE';",
                    conn
                );

                using var reader = await command.ExecuteReaderAsync();

                var schemas = new List<string>();
                while (await reader.ReadAsync(cancellationToken))
                {
                    schemas.Add(reader.GetString(0));
                }

                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = JsonConvert.SerializeObject(schemas, settings) }]
                };
            }
            catch (Exception ex)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 这是供人工智能执行查询的工具
        /// </summary>
        /// <param name="url"></param>
        /// <param name="query"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "sqlserver_execute_query"), Description("[SqlServer]执行查询操作，并将结果以 JSON 格式返回")]
        public async Task<CallToolResult> ExecuteQuery([Description("SQL查询条件")] string query
            , [Description("最大数据条数")] int? max_rows = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            var maxRows = max_rows ?? 100;
            try
            {
                await using var conn = GetSqlServerConnection(url);
                await conn.OpenAsync(cancellationToken);

                using var command = new SqlCommand(query, conn);
                using var reader = await command.ExecuteReaderAsync();

                var list = new List<Dictionary<string, object?>>();
                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };

                int count = 0;
                while (await reader.ReadAsync(cancellationToken) && count < maxRows)
                {
                    var row = new Dictionary<string, object?>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var val = reader.GetValue(i);
                        var str = val?.ToString() ?? null;
                        if (str != null && str.Length > MutiDBConns?.MaxLongData)
                            str = str.Substring(0, MutiDBConns?.MaxLongData ?? 100);
                        row[reader.GetName(i)] = str;
                    }
                    list.Add(row);
                    count++;
                }
                var text = JsonConvert.SerializeObject(list, settings);
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = text }]
                };
            }
            catch (Exception ex)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 这是供人工智能测试数据库连接的工具
        /// </summary>
        /// <returns></returns>
        [McpServerTool(Name = "sqlserver_health_check"), Description("[SqlServer]检查数据库连接是否正常且可用")]
        public async Task<CallToolResult> HealthCheck([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetSqlServerConnection(url);
                await conn.OpenAsync(cancellationToken);

                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = "true" }]
                };
            }
            catch (Exception e)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = "false" }]
                };
            }
        }

        /// <summary>
        /// 正则表达式在查询开始时检测有效的T-SQL关键字
        /// </summary>
        private static readonly Regex ValidTSqlStartPattern = new(
            @"^\s*(SELECT|INSERT|UPDATE|DELETE|WITH|CREATE|ALTER|DROP|GRANT|REVOKE|EXEC|EXECUTE|DECLARE|SET|USE|BACKUP|RESTORE|TRUNCATE|MERGE)\s+"
           , RegexOptions.IgnoreCase | RegexOptions.Compiled);

        /// <summary>
        /// 对连接的Microsoft SQL Server数据库执行T-SQL查询。
        /// </summary>
        /// <param name="query"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "sqlserver_execute_sql"), Description(@"[SqlServer]对连接的Microsoft SQL Server数据库执行T-SQL查询。
    重要提示：此工具仅接受Microsoft SQL Server的有效T-SQL （Transact-SQL）语法。
    支持的操作:
      —选择用于数据检索的语句
      -插入，更新，删除数据修改
      -创建、修改、删除模式更改
      -用于CTEs（公共表表达式）的子句
      -存储过程的EXEC/EXECUTE
      -和其他有效的T-SQL语句
    有效的T-SQL示例：
      - SELECT * FROM Active = 1的用户
      -插入产品（名称，价格）值（'Widget', 19.99）
      -更新ID = 123的客户设置状态为‘Active’
      -创建订单表（ID int为主键，CustomerID int）
    查询参数必须只包含T-SQL语句，不能包含解释、markdown或其他文本。")]
        public async Task<CallToolResult> ExecuteSql([Description(@"要执行的T-SQL查询。必须是有效的Microsoft SQL Server T-SQL语法。
例如：'SELECT * FROM Users'， 'INSERT INTO Products VALUES (1, "" Name "")', 'CREATE TABLE Test (ID int)'，不要包含解释、markdown格式或非sql文本。")] string query
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            _logger.LogInformation("收到SQL执行请求。查询长度：{QueryLength}", query.Length);

            if (string.IsNullOrWhiteSpace(query))
            {
                _logger.LogWarning("接收到空或null查询");
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = "错误：SQL查询不能为空" }]
                };
            }

            // 验证查询是否像T-SQL
            var trimmedQuery = query.Trim();
            if (!ValidTSqlStartPattern.IsMatch(trimmedQuery))
            {
                _logger.LogWarning("收到无效的T-SQL查询。查询不以有效的T-SQL关键字{QueryStart}开头", trimmedQuery.Length > 50 ? trimmedQuery[..50] + "..." : trimmedQuery);

                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = @"错误：无效的T-SQL语法。此工具只接受有效的Microsoft SQL Server T-SQL语句。
有效的T-SQL语句必须以如下关键字开头：
  - SELECT（用于数据检索）
  -插入、更新、删除（用于数据修改）
  - CREATE， ALTER， DROP（用于模式更改）
  - WITH（适用于cte）
  - EXEC/EXECUTE（用于存储过程）
  -和其他有效的T-SQL关键字

例子:
  ✓选择* FROM Users
  ✓插入Products (Name) VALUES （'Test'）
  ✓创建表Orders （ID int）

请告诉我所有的用户
你能×创建一个订单表吗？
√sql SELECT * FROM Users`

请只提供T-SQL语句，没有解释或格式化。" }]
                };
            }

            try
            {
                _logger.LogInformation("执行以{QueryStart}开头的T-SQL查询", trimmedQuery.Length > 30 ? trimmedQuery[..30] + "..." : trimmedQuery);

                await using var conn = GetSqlServerConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var command = new SqlCommand(query, conn);

                // 确定这是选择查询还是命令
                var isSelectQuery = trimmedQuery.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) || trimmedQuery.StartsWith("WITH", StringComparison.OrdinalIgnoreCase);
                if (isSelectQuery)
                {
                    // 处理SELECT查询——返回数据
                    await using var reader = await command.ExecuteReaderAsync(cancellationToken);
                    var result = await FormatQueryResults(reader, cancellationToken);
                    _logger.LogInformation("SELECT查询已成功执行");
                    return new CallToolResult
                    {
                        IsError = false,
                        Content = [new TextContentBlock() { Type = "text", Text = result }]
                    };
                }
                else
                {
                    // 处理插入/更新/删除/DDL -返回受影响的行
                    var rowsAffected = await command.ExecuteNonQueryAsync(cancellationToken);
                    var result = $"查询成功。行受影响: {rowsAffected}";
                    _logger.LogInformation("非select查询执行成功。受影响的行：{RowsAffected}", rowsAffected);
                    return new CallToolResult
                    {
                        IsError = false,
                        Content = [new TextContentBlock() { Type = "text", Text = result }]
                    };
                }
            }
            catch (SqlException ex)
            {
                _logger.LogError(ex, "SQL执行失败，出现SQL错误：{SqlErrorMessage}", ex.Message);
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = $"SQL Error: {ex.Message}" }]
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "SQL执行失败，出现一般错误：{ErrorMessage}", ex.Message);
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = $"Error: {ex.Message}" }]
                };
            }
        }

        /// <summary>
        /// 列出数据库中包含基本信息的所有表。
        /// </summary>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "sqlserver_get_tables"), Description("[SqlServer]列出数据库中包含基本信息的所有表。")]
        public async Task<CallToolResult> GetTables([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetSqlServerConnection(url);
                await conn.OpenAsync(cancellationToken);

                var query = @"
                SELECT 
                    t.TABLE_SCHEMA,
                    t.TABLE_NAME,
                    t.TABLE_TYPE,
                    ISNULL(p.rows, 0) as ROW_COUNT
                FROM INFORMATION_SCHEMA.TABLES t
                LEFT JOIN (
                    SELECT 
                        SCHEMA_NAME(o.schema_id) as schema_name,
                        o.name as table_name,
                        SUM(p.rows) as rows
                    FROM sys.objects o
                    JOIN sys.partitions p ON o.object_id = p.object_id
                    WHERE o.type = 'U' AND p.index_id IN (0,1)
                    GROUP BY o.schema_id, o.name
                ) p ON t.TABLE_SCHEMA = p.schema_name AND t.TABLE_NAME = p.table_name
                WHERE t.TABLE_TYPE = 'BASE TABLE'
                ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME";

                await using var command = new SqlCommand(query, conn);
                await using var reader = await command.ExecuteReaderAsync(cancellationToken);

                string reuslt = await FormatQueryResults(reader, cancellationToken);
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = reuslt }]
                };
            }
            catch (Exception ex)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = $"错误表列表: {ex.Message}" }]
                };
            }
        }

        /// <summary>
        /// 列出SQL Server实例中可用的所有模式
        /// </summary>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "sqlserver_get_schemas"), Description("[SqlServer]列出SQL Server实例中可用的所有模式（数据库）")]
        public async Task<CallToolResult> GetSchemas([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetSqlServerConnection(url);
                await conn.OpenAsync(cancellationToken);

                var query = @"
                SELECT 
                    SCHEMA_NAME,
                    SCHEMA_OWNER,
                    DEFAULT_CHARACTER_SET_CATALOG,
                    DEFAULT_CHARACTER_SET_SCHEMA,
                    DEFAULT_CHARACTER_SET_NAME
                FROM INFORMATION_SCHEMA.SCHEMATA
                ORDER BY SCHEMA_NAME";
                await using var command = new SqlCommand(query, conn);
                await using var reader = await command.ExecuteReaderAsync(cancellationToken);

                string reuslt = await FormatQueryResults(reader, cancellationToken);
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = reuslt }]
                };
            }
            catch (Exception ex)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = $"错误模式列表: {ex.Message}" }]
                };
            }
        }

        /// <summary>
        /// 格式化查询结果
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        private static async Task<string> FormatQueryResults(SqlDataReader reader, CancellationToken cancellationToken)
        {
            var result = new StringBuilder();

            if (!reader.HasRows)
            {
                return "查询执行成功，未返回行。";
            }

            // 获取列标题
            var columnCount = reader.FieldCount;
            var columnNames = new string[columnCount];
            var columnWidths = new int[columnCount];

            for (int i = 0; i < columnCount; i++)
            {
                columnNames[i] = reader.GetName(i);
                //最小宽度为 10
                columnWidths[i] = Math.Max(columnNames[i].Length, 10);
            }

            // 读取所有行以确定列宽
            var rows = new List<object[]>();
            while (await reader.ReadAsync(cancellationToken))
            {
                var row = new object[columnCount];
                for (int i = 0; i < columnCount; i++)
                {
                    row[i] = reader.IsDBNull(i) ? "NULL" : reader.GetValue(i);
                    var valueLength = row[i].ToString()?.Length ?? 4;
                    columnWidths[i] = Math.Max(columnWidths[i], valueLength);
                }
                rows.Add(row);
            }

            // 构建头部信息
            result.AppendLine(string.Join(" | ", columnNames.Select((name, i) => name.PadRight(columnWidths[i]))));
            result.AppendLine(string.Join("-+-", columnWidths.Select(w => new string('-', w))));

            // 构建数据行
            foreach (var row in rows)
            {
                result.AppendLine(string.Join(" | ", row.Select((value, i) => (value.ToString() ?? "NULL").PadRight(columnWidths[i]))));
            }

            result.AppendLine($"\n({rows.Count} 行数量返回)");

            return result.ToString();
        }
    }
}
